﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CommunitySystemModel;
using System.Data;
using System.Collections.ObjectModel;

namespace CommunitySystem.BO
{
   public class JSCJBO
    {
       public bool SaveJY(Emp_CJJS entity, bool isAdd)
       {
           string sql = string.Empty;
           if (isAdd)
               sql = string.Format(@"INSERT INTO Emp_CJJS
           ([Name],[EID],[CardNo],[OldDW],[OldKS],[OldZW],[OldZWCC],[OldZWSX],[NewDW]
           ,[DWID],[BGRQ],[NewKS],[DutyName],[DutyLevel],[DutyAttribute] ,[CreateUser],[CreateDate])
     VALUES
           ('{0}',{1},'{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}') select @@IDENTITY"
                   , entity.Name,entity.Eid,entity.CardNo,entity.OldDW,entity.OldKS,entity.OldZW,entity.OldZWCC,entity.OldZWSX,entity.NewDW
                   ,entity.DWID,entity.BGRQ,entity.NewKS,entity.DutyName,entity.DutyLevel,entity.DutyAttribute,entity.CreateUser,entity.CreateDate);
           else
               sql = string.Format(@"UPDATE Emp_CJJS
   SET [Name] = '{0}',[EID] ={1},[CardNo] ='{2}',[OldDW] = '{3}',[OldKS] = '{4}',[OldZW] ='{5}'
      ,[OldZWCC] = '{6}',[OldZWSX] ='{7}',[NewDW] ='{8}',[DWID] ={9},[BGRQ] = '{10}',[NewKS] = '{11}',[DutyName] ='{12}'
      ,[DutyLevel] = '{13}',[DutyAttribute] = '{14}',[CreateUser] = '{15}',[CreateDate] ='{16}' WHERE ID={17} select {17}"
                   , entity.Name, entity.Eid, entity.CardNo, entity.OldDW, entity.OldKS, entity.OldZW, entity.OldZWCC, entity.OldZWSX, entity.NewDW
                   , entity.DWID, entity.BGRQ, entity.NewKS, entity.DutyName, entity.DutyLevel, entity.DutyAttribute, entity.CreateUser, entity.CreateDate,entity.Id);


           int result = Convert.ToInt32(AccessHelper.GetInstance().ExcuteDataSet(sql).Tables[0].Rows[0][0]);
           if (result > 0)
           {
                //人员信息职务信息里增加一条：
                //职务名称：提取上项       科室名称：部门领导（处级干部统一为部门领导）
                //职务层次：提取上项       职务属性：提取上项
                //批准时间：提取身份变更日期     任职机构：中共徐汇区委组织部
                //分管工作：空着
                //集体排序：空着    破格提拔：空着
                //任职方式：空着    任现职务层次时间：提取身份变更日期
                //是否连任：空着    任现职务属性时间：提取身份变更日期
                //任职状态：在任
                //简历加一条
                //添加后将其余职务都为“不在任”

               string sql1 = string.Empty;
               if (isAdd)
               {

                   sql1 = string.Format(@"update Emp_BasicInfo set OrgID=(select top 1 ID  from OrgForHR where Name='{9}'),PositionName ='部门领导' where ID={0};
                    update Emp_Duty set JobStatus='不在任',JSCJID={8} where JobStatus='在任' and EmpbasicID={0};
                    insert into Emp_Duty (EmpbasicID,DutyNo,DutyName,DutyLevel,DutyAttribute,OKDate,OrganizationName,RXRZWSXSJ,JobDate,JobStatus,JSCJID)
                    values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{5}','{5}','{7}',{8})
                  ", entity.Eid, entity.DutyName, "部门领导", entity.DutyLevel, entity.DutyAttribute, Convert.ToDateTime(entity.BGRQ), "中共徐汇区委组织部", "在任", result,entity.NewDW);

                   sql1 += string.Format(@"
                    update Emp_Resume set EndDate='{1}',JSCJID={4} where (EndDate='' or EndDate is null) and EmpbasicID={0};
                    insert into Emp_Resume (EmpbasicID,BeginDate,EndDate,Unit,Duty,IsPeriod,JSCJID)
                    values({0},'{1}',null,'{2}','{3}',1,{4})", entity.Eid, Convert.ToDateTime(entity.BGRQ), entity.NewDW, entity.DutyName, result);
               }
               else
               {
                   sql1 = string.Format(@"
                        update Emp_Duty set EmpbasicID={0},DutyNo='{1}',DutyName='{2}',DutyLevel='{3}'
                        ,DutyAttribute='{4}',OKDate='{5}',OrganizationName='中共徐汇区委组织部',RXRZWSXSJ='{5}',JobDate='{5}'
                        where JSCJID={6} and JobStatus='在任'"
                       , entity.Eid, entity.DutyName, "部门领导", entity.DutyLevel, entity.DutyAttribute, Convert.ToDateTime(entity.BGRQ), result);

                   sql1 = string.Format(@"update Emp_Resume set EndDate='{1}' where  EmpbasicID={0} and JSCJID={4} and (EndDate!='' or EndDate is not null);
                        update Emp_Resume  set EmpbasicID={0},BeginDate='{1}',EndDate=null,Unit='{2}',Duty='{3}',IsPeriod=1
                        where JSCJID={4}", entity.Eid, Convert.ToDateTime(entity.BGRQ), entity.NewDW, entity.DutyName, result);
               }
               if (!string.IsNullOrEmpty(sql1))
               {
                   if (AccessHelper.GetInstance().ExecuteNonQuery(sql1) > 0)
                       return true;
                   else return false;
               }
               else
                   return true;
           }
           else return false;
       }
       public bool JYDel(Emp_CJJS entity)
       {
           string sql = string.Empty;
           sql = string.Format(@"update Emp_BasicInfo set OrgID=(select top 1 ID  from OrgForHR where Name='{0}'),PositionName ='{1}' where ID={2};
                    delete Emp_Duty where JSCJID in ({3}) and  JobStatus='在任';
                    update Emp_Duty set JobStatus='在任' where JSCJID in ({3}) and  JobStatus='不在任';
                    delete Emp_Resume where JSCJID in ({3}) and (EndDate='' or EndDate is null);
                    update Emp_Resume set EndDate=null where JSCJID in ({3}) and (EndDate!='' or EndDate is not null)", entity.OldDW,entity.OldKS,entity.Eid,entity.Id);
           int result = AccessHelper.GetInstance().ExecuteNonQuery(sql);
           if (result > 0)
           {
             AccessHelper.GetInstance().ExecuteNonQuery(string.Format("delete emp_cjjs where id in({0})",entity.Id));
             return true;
           }
           else return false;
       }
       public ObservableCollection<Emp_CJJS> GetJY(string con, int index, int pagesize)
       {
           ObservableCollection<Emp_CJJS> rights = new ObservableCollection<Emp_CJJS>();
           string sql = string.Format(@"select top({0}) ROW_NUMBER() over(order by ID desc) as Sequence,IsSelected='false'
                                        ,* from
                                        (
                                        select ROW_NUMBER() over(order by ID desc) as rowsID,* from Emp_CJJS emp where 1=1 {2}
                                        ) a where a.rowsID>=
                                        (
                                        SELECT ISNULL(MAX(rowsID),0) FROM 
                                        (
                                        select top({0}*({1}-1)+1) * from
                                        (
                                        select ROW_NUMBER() over(order by ID desc) as rowsID,* from Emp_CJJS emp where 1=1 {2}
                                        )b
                                        )c
                                        ) order by CreateDate desc", pagesize, index, con);
           DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
           foreach (DataRow dr in ds.Tables[0].Rows)
           {
               rights.Add(Emp_CJJS.FillEntity(dr));
           }
           return rights;
       }
       public int GetJYCount(string con)
       {
           string sql = @"select  COUNT(id) from Emp_CJJS emp where 1=1";
           if (!string.IsNullOrEmpty(con))
           {
               sql += con;
           }

           DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
           return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
       }
    }
}
